//
//  SpatialDataCache.m
//  geom
//
//  Created by 徐洋 on 2018/8/31.
//  Copyright © 2018年 徐洋. All rights reserved.
//

#import "SpatialiteDataCache.h"

#import "FMDatabase.h"

#include <sqlite3.h>
#include "spatialite.h"

@implementation SpatialObject

+ (instancetype)spatialObjectWithBlob:(NSData *)blob {
    gaiaGeomCollPtr geomColl = gaiaFromSpatiaLiteBlobWkb([blob bytes], (int)blob.length);
    SpatialObject * so = [[SpatialObject alloc] init];
    so.geometry = geomColl;
    return so;
}

- (void)dealloc {
    if (self.geometry) {
        gaiaFreeGeomColl(self.geometry);
    }
}

@end

@interface SpatialiteDataCache()

@end

@implementation SpatialiteDataCache

- (id)initWithSpaliteFile:(NSString *)dbFile {
    self = [super init];
    if (self) {
        _srid = 3857;
        _databaseFile = dbFile;
    }
    return self;
}

- (void)refreshCache {
//    [self loadAirspaces];
//    [self loadRestrictedAreas];
    [self loadAirports];
//    [self loadAirwaySegments];
}

- (NSString *)buildSQLWithTable:(NSString *)tableName
{
    float xmin = CGRectGetMinX(self.bounds);
    float ymin = CGRectGetMinY(self.bounds);
    float xmax = CGRectGetMaxX(self.bounds);
    float ymax = CGRectGetMaxY(self.bounds);

    NSString * sql = [NSString stringWithFormat:@"select ST_Transform(Geometry, %d) as geom, ST_Transform(GeomFromText('POLYGON((%f %f, %f %f, %f %f, %f %f))', %d), 4326) as g1"
                @" from %@ where rowid in ("
                @" select pkid from idx_%@_Geometry"
                @" where xmin>MbrMinX(g1) and xmax<MbrMaxX(g1) and ymin>MbrMinY(g1) and ymax<MbrMaxY(g1)"
                @")", self.srid,
              xmin, ymin, xmin, ymax, xmax, ymax, xmax, ymin, self.srid,
              tableName, tableName];
    return sql;
}


- (void)loadAirspaces {
    NSString * sql = [self buildSQLWithTable:@"AIRSPACE"];
    NSArray * result = [self retreiveGeomObjectsWithSql:sql];
    if (result) {
        self.arrAirspaces = result;
    }
}

- (void)loadAirports {
    NSString * sql = [self buildSQLWithTable:@"AIRPORT"];
    NSArray * result = [self retreiveGeomObjectsWithSql:sql];
    if (result) {
        NSLog(@"Load %d airports.", (int)result.count);
        self.arrAirports = result;
    }
}

- (void)loadRestrictedAreas {
    NSString * sql = [self buildSQLWithTable:@"RESTRICTED"];
    NSArray * result = [self retreiveGeomObjectsWithSql:sql];
    if (result) {
        self.arrRestrictedAreas = result;
    }
}

- (void)loadAirwaySegments {
    NSString * sql = [self buildSQLWithTable:@"SEGMENT"];
    NSArray * result = [self retreiveGeomObjectsWithSql:sql];
    if (result) {
        self.arrAirwaySegments = result;
    }
}

- (NSArray *)retreiveGeomObjectsWithSql:(NSString *)sql {
    
    NSMutableArray * retArr = [NSMutableArray array];
    
    FMDatabase * db = [FMDatabase databaseWithPath:self.databaseFile];
    if (!db) {
        NSLog(@"Create DB Error: %@", [db lastError]);
        return nil;
    }
    
    if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
        NSLog(@"Open DB Error: %@", [db lastError]);
        return nil;
    }
            
    void * spatial_conn = spatialite_alloc_connection();
    
    sqlite3 * sqliteHandler = [db sqliteHandle];
    
    spatialite_init_ex(sqliteHandler, spatial_conn, 0);
    
    FMResultSet * result = [db executeQuery:sql];
    if (!result) {
        NSLog(@"Query DB Error: %@", [db lastError]);
        return [NSArray arrayWithArray:retArr];
    }
    while ([result next]) {
        
        NSData * blob = [result dataForColumn:@"geom"];
        SpatialObject * so = [SpatialObject spatialObjectWithBlob:blob];
        [retArr addObject:so];
    }
    
    spatialite_cleanup_ex(spatial_conn);
    [result close];
    [db close];

    return [NSArray arrayWithArray:retArr];
}

@end
